﻿Imports System.Data.OleDb

Public Class User_PagesNameManagement
    Inherits ConnectionClass

    Public Sub InsertUserPageName(ByVal UserId As Int32, ByVal PageNameId As Int32)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT INTO Users_PagesNames(UserId,PageNameId) VALUES(@f1,@f2)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", UserId)
            newCmd.Parameters.AddWithValue("@f2", PageNameId)

            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub



    Public Function SelectNumberOfVistorsForAllTimeByPageContent(ByVal pageContent As String) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  COUNT(Users_PagesNames.UserId) AS visitors, PagesNames.PageNameContent FROM    (Users_PagesNames INNER JOIN PagesNames ON Users_PagesNames.PageNameId = PagesNames.PageNameId) WHERE  (PagesNames.PageNameContent LIKE '%' + @f1 + '%') GROUP BY PagesNames.PageNameContent"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", pageContent)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectNumberOfVistorByPageContentByYearMonth(ByVal pageContent As String, ByVal year As Int32, ByVal month As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  COUNT(Users_PagesNames.UserId) AS visitors, PagesNames.PageNameContent FROM (Users_PagesNames INNER JOIN PagesNames ON Users_PagesNames.PageNameId = PagesNames.PageNameId) WHERE        (PagesNames.PageNameContent LIKE '%' + @f1 + '%') AND (DATEPART('yyyy', Users_PagesNames.User_PageNameDate) = @f2) AND (DATEPART('m',  Users_PagesNames.User_PageNameDate) = @f3) GROUP BY PagesNames.PageNameContent"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", pageContent)
        cmd.Parameters.AddWithValue("@f2", year)
        cmd.Parameters.AddWithValue("@f3", month)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectNumberOfVistorByPageContentByMonthAllYears(ByVal pageContent As String, ByVal month As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  COUNT(Users_PagesNames.UserId) AS visitors, PagesNames.PageNameContent FROM (Users_PagesNames INNER JOIN PagesNames ON Users_PagesNames.PageNameId = PagesNames.PageNameId) WHERE   (PagesNames.PageNameContent LIKE '%' + @f1 + '%') AND (DATEPART('m',  Users_PagesNames.User_PageNameDate) = @f3) GROUP BY PagesNames.PageNameContent"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", pageContent)
            cmd.Parameters.AddWithValue("@f3", month)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectNumberOfVistorsByPageContentByYearMonthAll(ByVal pageContent As String, ByVal year As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  COUNT(Users_PagesNames.UserId) AS visitors, PagesNames.PageNameContent FROM (Users_PagesNames INNER JOIN PagesNames ON Users_PagesNames.PageNameId = PagesNames.PageNameId) WHERE        (PagesNames.PageNameContent LIKE '%' + @f1 + '%') AND (DATEPART('yyyy', Users_PagesNames.User_PageNameDate) = @f2)  GROUP BY PagesNames.PageNameContent"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", pageContent)
            cmd.Parameters.AddWithValue("@f2", year)

            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
